
*--- 1) How much did the EL appreciate? 
use "${datadir}\HCA_full.dta", clear
keep if dated <= mdy(4, 2, 2015)
merge 1:1 schemeid using ///
	"${datadir}\HCA_redemptions_all", keep(1 3) ///
	keepusing(Datepurchased HCACash OriginalHCAshare Transactiontype ///
	Completion merge_htb_main) gen(merge_repaid)


// merge in appreciation data
// Match the local authorities
merge m:1 pcd using ///
	"${datadir}\la_region_postcode_lookup", ///
	keep(1 3) nogen
ren localauthoritycode areacode 

// Appreciation all
replace datem = datem + 24
merge m:1 datem areacode using /// 
	"${datadir}\local_price_appreciation", ///
	keep(1 3) nogen keepusing(appreciation2)

rename datem datem2y
rename appreciation2 fw_appreciation2
gen datem = datem2y - 24
	
	
merge m:1 datem areacode using /// 
	"${datadir}\local_price_appreciation", ///
	keep(1 3) nogen keepusing(appreciation2)
		
rename appreciation2 bw_appreciation2


// Appreciation new
gen dateq = qofd(dated) + 8
ren areacode laua
merge m:1 dateq laua using ///
	"${datadir}\la_prevnew_appreciation", keep(1 3) 

ren dateq dateq2y
gen dateq = dateq - 8



// local house price appreciation: chart for later
gen dhp = (fw_appreciation2+1)^(1/2) -1
replace dhp = dhp * 100

* NUMBER FOR NOTES TO THE TABLE
count if !mi(dhp) & !regexm(Transactiontype, "staircasing|Sale")	
count if !mi(dhp) & Transactiontype == "Sale"	
count if !mi(dhp) & regexm(Transactiontype, "staircasing")	

twoway  (kdensity dhp if !regexm(Transactiontype, "staircasing|Sale") ///
	& inrange(dhp,-10,20), bw(1) lpattern(dot)) ///
	(kdensity dhp if Transactiontype == "Sale"  & inrange(dhp,-10,20), bw(1) lpattern(dash)) ///
	(kdensity dhp if regexm(Transactiontype, "staircasing") & inrange(dhp,-10,20), bw(1) ), ///
  xtitle("Local house price appreciation (%)") ytitle("Kernel density") ///
  graphregion(color(white)) bgcolor(white) name(gr1hp, replace) ///
  leg(order(1 "EL not repaid" 2 "EL repaid (sale)" 3 "EL repaid (staircasing)")) 
	

// idiosyncratic appreciation: sales
gen dhpn = (appreciation2new + 1)^(1/2) - 1
replace dhpn = dhpn * 100

gen diff = Completion - Datepurchased
gen dhp_a = HCACash / OriginalHCAshare 
gen diff_year = diff / 365
gen appr = ( dhp_a^(1 / diff_year ) - 1 ) * 100


twoway ///
	(kdensity dhpn if Transactiontype == "Sale" & inrange(dhpn, -10, 30), ///
	bw(1) lpattern(dash)) ///
	(kdensity appr if Transactiontype == "Sale" & inrange(appr, -10, 30), ///
	bw(1) lpattern(solid) lwidth(thick)), ///
  xtitle("Local house price appreciation (%)") ytitle("Kernel density") ///
  graphregion(color(white)) bgcolor(white) ///
  ti("EL repaid (sale)") ///
  leg(order(1 "Local prices (new properties)" ///
  2 "Individual prices") r(2)) name(gr1, replace) 

	
// idiosyncratic appreciation: staircasing
twoway ///
	(kdensity dhpn if regexm(Transactiontype, "staircasing") & inrange(dhpn, -10, 30), ///
	bw(1) lpattern(dash)) ///
	(kdensity appr if regexm(Transactiontype, "staircasing") & inrange(appr, -10, 30), ///
	bw(1) lpattern(solid) lwidth(thick)), ///
  xtitle("Local house price appreciation (%)") ytitle("Kernel density") ///
  graphregion(color(white)) bgcolor(white) ///
  ti("EL repaid (staircasing)") ///
  leg(order(1 "Local prices (new properties)" ///
  3 "Individual prices") r(2)) name(gr2, replace) 


gr combine gr1 gr2, graphregion(color(white)) xsize(8) ycommon
graph export ///
	"${figuredir}\kdensity_appreciation2_idiosync.pdf", ///
	replace 



	
	
	
*--- 2) Information from actual remortgages

// prepare data
use "${datadir}\HTB_repayment", replace
merge 1:1 schemeid using "${datadir}\HCA_redemptions", ///
	keep(1 3) gen(merge_redemption)

merge 1:1 psd_id using "${datadir}\psd001_rmg_main", ///
	gen(merge_remortgage) ///
	keepusing(next_psd_id next_income next_loan next_date) keep(1 3)

// income growth
gen income_g = ( (next_income - gross_income)/gross_income +1)^(1/2) -1
replace income_g = income_g * 100


preserve 
	keep if inrange(income_g, -50, 100)
	twoway  (kdensity income_g if merge_red == 1, lpattern(dash) bw(5)) ///
	 (kdensity income_g if merge_red == 3, bw(5)), ///
	  xtitle("Income growth (%) between initial mortgage and refinance") ytitle("Kernel density") ///
	  graphregion(color(white)) bgcolor(white) ///
	  leg(order(1 "EL not repaid" 2 "EL repaid")) name(gr2inc, replace)
restore


// Combined chart
gr combine gr1hp gr2inc, xsize(8) graphregion(color(white))
graph export ///
	"${figuredir}\kdensity_hp_inc.pdf", replace 
	

// Equity extraction	
gen month = round( (next_date - date) / 30 )

gen lb 	= loan_final * (1+interest00/12)^month ///
		-  payment * (((1+interest00/12)^month - 1)/(interest00/12) ) 

gen equity_ex = next_loan - lb 
replace equity_ex = next_loan - loan_final if missing(equity_ex)

replace HCACash = HCACash / 1000
	
twoway  (kdensity equity_ex if merge_red == 3 & equity_ex < 200) ///
(kdensity HCACash if merge_red == 3 & HCACash < 200, lpattern(dash)), ///
	xtitle("Thousands of £") ytitle("Kernel density") ///
	graphregion(color(white)) bgcolor(white) ///
	leg(order(1 "Extracted equity" 2 "EL (revalued)")) ///
	xlabel(-100(100)200, format(%8.0fc))
	
graph export ///
	"${figuredir}\kdensity_remortgage_pound.pdf", ///
	replace 




// LTVs
gen loan_val_counter = cond(!mi(lb), lb, loan_final)
gen ltv_counter = loan_val_counter / next_property_val

gen next_ltv = next_loan / next_property_val

gen diff_ltv = next_ltv - ltv_counter

// additional equity extraction on top of repaying EL
gen add_equity_ex = (equity_ex - HCACash) / next_property_val

// with additional equity extraction		
twoway 	(kdensity add_equity_ex if merge_red == 3 ///
	& add_equity_ex > -.2 /*& HCACash < 200*/) /// 
	(kdensity diff_ltv if merge_redemption == 3, ///
	msize(medsmall) sort lpattern(solid) lpattern(dash)), ///
	xline(0, lwidth(vthin) lcolor(black) lpattern(dash)) ///
	xline(.2, lwidth(vthin) lcolor(black) lpattern(dash)) ///
	leg(order(1 "Minus EL share" 2 "Actual")) ///
	xtitle("Difference actual and counterfactual LTV (%)") ///
	graphregion(color(white)) bgcolor(white) 
		
graph export "0 - Shared Appreciation\Figure\kdensity_remortgage_pound_LTV.pdf", ///
	as(pdf) replace	